package com.lagou.spark

import org.apache.spark.sql.{DataFrame, SparkSession}

/*
A表有三个字段：ID、startdate、enddate，有3条数据
1 2019-03-04 2020-02-03
2 2020-04-05 2020-08-04
3 2019-10-09 2020-06-11
写SQL（需要SQL和DSL）将以上数据变化为
2019-03-04	2019-10-09
2019-10-09	2020-02-03
2020-02-03	2020-04-05
2020-04-05	2020-06-11
2020-06-11	2020-08-04
2020-08-04	2020-08-04
 */
object Table {
  def main(args: Array[String]): Unit = {
    //创建SparkSession
    val spark = SparkSession.builder()
      .appName(this.getClass.getCanonicalName)
      .master("local[*]")
      .getOrCreate()
    spark.sparkContext.setLogLevel("warn")

    import org.apache.spark.sql.functions._
    import spark.implicits._

    //准备数据
    val list = List((1, "2019-03-04", "2020-02-03"), (2, "2020-04-05", "2020-08-04"), (3, "2019-10-09", "2020-06-11"))
    val df: DataFrame = spark.createDataFrame(list).toDF("id", "startdate", "enddate")

    //创建一个dataframe求并集用于后面计算
    val ds: DataFrame = df.select("startdate").union(df.select("enddate"))
    ds.createOrReplaceTempView("t1")
    
	//SQL
    spark.sql(
      """
        |select startdate, nvl(lead(startdate) over(order by startdate), startdate) enddate
        |from (select * from t1) t2
        |""".stripMargin
    ).show()

    //DSL
    val res: DataFrame = ds.select(
      $"startdate",
      expr("nvl(lead(startdate) over(order by startdate), startdate) ") as "enddate"
    )
    res.show()

    //关闭SparkSession
    spark.close()
  }
}
